package com.example.lianjiaproject.dao;

import com.example.lianjiaproject.pojo.Floor_type;
import com.example.lianjiaproject.pojo.Name_value;
import com.example.lianjiaproject.pojo.Subway_type;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;

import java.util.List;

@Mapper
public interface SpiderDao {
    @Select("select region,ROUND(AVG(unit_price)) from spider where floor ${floor} and lift ${lift} and unit_price between #{min} and #{max} and rent ${rent} and region ${region} GROUP BY region ORDER BY ROUND(AVG(unit_price)) desc")
    List<Name_value> AVGList(double min,double max,String rent,String region,String floor,String lift);

    @Select("select region,count(region) from spider where floor ${floor} and lift ${lift} and unit_price between #{min} and #{max} and rent ${rent} and region ${region} GROUP BY region ORDER BY count(region) desc")
    List<Name_value> CountList(double min,double max,String rent,String region,String floor,String lift);

    @Select("select orientation,count(orientation) as num from spider where floor ${floor} and lift ${lift} and unit_price between #{min} and #{max} and rent ${rent} and region ${region} GROUP BY orientation ORDER BY num desc")
    List<Name_value> orientation_Count(double min,double max,String rent,String region,String floor,String lift);

    @Select("select case\n" +
            "when unit_price between #{min} and #{min}+(#{max}-#{min})/5*1-1 then CONCAT(FLOOR(#{min}),'-',FLOOR(#{min}+(#{max}-#{min})/5*1-1))\n" +
            "when unit_price between #{min}+(#{max}-#{min})/5*1 and #{min}+(#{max}-#{min})/5*2-1 then CONCAT(FLOOR(#{min}+(#{max}-#{min})/5*1),'-',FLOOR(#{min}+(#{max}-#{min})/5*2-1))\n" +
            "when unit_price between #{min}+(#{max}-#{min})/5*2 and #{min}+(#{max}-#{min})/5*3-1 then CONCAT(FLOOR(#{min}+(#{max}-#{min})/5*2),'-',FLOOR(#{min}+(#{max}-#{min})/5*3-1))\n" +
            "when unit_price between #{min}+(#{max}-#{min})/5*3 and #{min}+(#{max}-#{min})/5*4-1 then CONCAT(FLOOR(#{min}+(#{max}-#{min})/5*3),'-',FLOOR(#{min}+(#{max}-#{min})/5*4-1))\n" +
            "when unit_price between #{min}+(#{max}-#{min})/5*4 and #{min}+(#{max}-#{min})/5*5 then CONCAT(FLOOR(#{min}+(#{max}-#{min})/5*4),'-',FLOOR(#{min}+(#{max}-#{min})/5*5))\n" +
            "end as a,COUNT(unit_price) as b from spider\n" +
            "where floor ${floor} and lift ${lift} and rent ${rent} and region ${region}\n"+
            "GROUP BY case\n" +
            "when unit_price between #{min} and #{min}+(#{max}-#{min})/5*1-1 then CONCAT(FLOOR(#{min}),'-',FLOOR(#{min}+(#{max}-#{min})/5*1-1))\n" +
            "when unit_price between #{min}+(#{max}-#{min})/5*1 and #{min}+(#{max}-#{min})/5*2-1 then CONCAT(FLOOR(#{min}+(#{max}-#{min})/5*1),'-',FLOOR(#{min}+(#{max}-#{min})/5*2-1))\n" +
            "when unit_price between #{min}+(#{max}-#{min})/5*2 and #{min}+(#{max}-#{min})/5*3-1 then CONCAT(FLOOR(#{min}+(#{max}-#{min})/5*2),'-',FLOOR(#{min}+(#{max}-#{min})/5*3-1))\n" +
            "when unit_price between #{min}+(#{max}-#{min})/5*3 and #{min}+(#{max}-#{min})/5*4-1 then CONCAT(FLOOR(#{min}+(#{max}-#{min})/5*3),'-',FLOOR(#{min}+(#{max}-#{min})/5*4-1))\n" +
            "when unit_price between #{min}+(#{max}-#{min})/5*4 and #{min}+(#{max}-#{min})/5*5 then CONCAT(FLOOR(#{min}+(#{max}-#{min})/5*4),'-',FLOOR(#{min}+(#{max}-#{min})/5*5))\n" +
            "end")
    List<Name_value> Level_Count(double min,double max,String rent,String region,String floor,String lift);

    @Select("select apartment_layout,COUNT(apartment_layout) from spider where floor ${floor} and lift ${lift} and unit_price between #{min} and #{max} and rent ${rent} and region ${region} GROUP BY apartment_layout order by COUNT(apartment_layout) desc")
    List<Name_value> apartment_layout_Count(double min,double max,String rent,String region,String floor,String lift);

    @Select("select region,COUNT(subway) from spider where floor ${floor} and lift ${lift} and unit_price between #{min} and #{max} and rent ${rent} and region ${region} and subway = '有' GROUP BY region order by COUNT(subway) desc")
    List<Name_value> Subway_Count(double min,double max,String rent,String region,String floor,String lift);

    @Select("SELECT case\n" +
            "when area < 50 then \"0-49\"\n" +
            "when area between 50 and 99 then \"50-99\"\n" +
            "when area between 100 and 149 then \"100-149\"\n" +
            "when area between 150 and 200 then \"150-200\"\n" +
            "when area > 200 then \">200\"\n" +
            "end as a,COUNT(area)\n" +
            "from spider\n" +
            "where floor ${floor} and lift ${lift} and rent ${rent} and region ${region} and unit_price between #{min} and #{max}\n"+
            "group by case\n" +
            "when area < 50 then \"0-49\"\n" +
            "when area between 50 and 99 then \"50-99\"\n" +
            "when area between 100 and 149 then \"100-149\"\n" +
            "when area between 150 and 200 then \"150-200\"\n" +
            "when area > 200 then \">200\"\n" +
            "end\n" +
            "ORDER BY COUNT(area) asc")
    List<Name_value> Area_Level_Count(double min,double max,String rent,String region,String floor,String lift);

    @Select("select region,ROUND(avg(area)) from spider where floor ${floor} and lift ${lift} and unit_price between #{min} and #{max} and rent ${rent} and region ${region} GROUP BY region order by avg(area) desc")
    List<Name_value> Avg_Area(double min,double max,String rent,String region,String floor,String lift);

    @Select("select IFNULL(IFNULL(a.region, b.region),c.region) as region,IFNULL(a.a, 0) as a,IFNULL(b.b, 0) as b,IFNULL(c.c, 0) as c from\n" +
            "(select region,COUNT(floor) as a FROM spider where floor=\"低楼层\" and rent ${rent} and region ${region} and unit_price between #{min} and #{max} GROUP BY region ) as a RIGHT JOIN\n" +
            "(select region,COUNT(floor) as b FROM spider where floor=\"中楼层\" and rent ${rent} and region ${region} and unit_price between #{min} and #{max} GROUP BY region) as b on a.region=b.region RIGHT JOIN\n" +
            "(select region,COUNT(floor) as c FROM spider where floor=\"高楼层\" and rent ${rent} and region ${region} and unit_price between #{min} and #{max} GROUP BY region) as c on b.region=c.region")
    List<Floor_type> Floor_type(double min,double max,String rent,String region,String floor,String lift);

    @Select("select IFNULL(a.region, b.region) as region,IFNULL(a.a, 0) as a,IFNULL(b.b, 0) as b from\n" +
            "(select region,ROUND(AVG(unit_price)) as a from spider where floor ${floor} and lift ${lift} and subway = \"有\" and rent ${rent} and region ${region} and unit_price between #{min} and #{max} GROUP BY region ORDER BY ROUND(AVG(unit_price)) DESC) as a RIGHT JOIN\n" +
            "(select region,ROUND(AVG(unit_price)) as b from spider where floor ${floor} and lift ${lift} and subway = \"无\" and rent ${rent} and region ${region} and unit_price between #{min} and #{max} GROUP BY region ORDER BY ROUND(AVG(unit_price)) DESC) as b on a.region=b.region")
    List<Subway_type> Subway_type(double min,double max,String rent,String region,String floor,String lift);

    @Select("select IFNULL(a.apartment_complex,b.apartment_complex) as apartment_complex,IFNULL(a.a,0),IFNULL(b.b,0) from\n" +
            "(select apartment_complex,COUNT(apartment_complex) as a from spider where floor ${floor} and lift ${lift} and region ${region} and rent = '整租' and unit_price between #{min} and #{max} GROUP BY apartment_complex) as a left JOIN\n" +
            "(select apartment_complex,COUNT(apartment_complex) as b from spider where floor ${floor} and lift ${lift} and region ${region} and rent = '合租' and unit_price between #{min} and #{max} GROUP BY apartment_complex) as b\n" +
            "on a.apartment_complex=b.apartment_complex\n" +
            "ORDER BY (IFNULL(a.a,0)+IFNULL(b.b,0)) DESC limit 10")
    List<Subway_type> apartment_complex_Count(double min,double max,String rent,String region,String floor,String lift);

    @Select("select apartment_layout,count(apartment_layout),ROUND(AVG(unit_price)) from spider where floor ${floor} and lift ${lift} and region ${region} and rent ${rent} and unit_price between #{min} and #{max} GROUP BY apartment_layout ORDER BY count(apartment_layout) desc limit 10")
    List<Subway_type> apartment_layout_price(double min,double max,String rent,String region,String floor,String lift);

    @Select("select rent,count(rent) from spider where floor ${floor} and lift ${lift} and  region ${region} and rent ${rent} and unit_price between #{min} and #{max} GROUP BY rent")
    List<Name_value> Rent_count(double min,double max,String rent,String region,String floor,String lift);

    @Select("SELECT case\n" +
            "when area < 50 then \"0-49\"\n" +
            "when area between 50 and 99 then \"50-99\"\n" +
            "when area between 100 and 149 then \"100-149\"\n" +
            "when area between 150 and 200 then \"150-200\"\n" +
            "when area > 200 then \">200\"\n" +
            "end as a,ROUND(AVG(unit_price))\n" +
            "from spider\n" +
            "where floor ${floor} and lift ${lift} and rent ${rent} and region ${region} and unit_price between #{min} and #{max}\n"+
            "group by case\n" +
            "when area < 50 then \"0-49\"\n" +
            "when area between 50 and 99 then \"50-99\"\n" +
            "when area between 100 and 149 then \"100-149\"\n" +
            "when area between 150 and 200 then \"150-200\"\n" +
            "when area > 200 then \">200\"\n" +
            "end\n" +
            "ORDER BY ROUND(AVG(unit_price)) asc")
    List<Name_value> Area_Price_AVG(double min,double max,String rent,String region,String floor,String lift);

    @Select("select * from\n" +
            "(select orientation as a,count(orientation) as b,ROUND(AVG(unit_price)) as c from spider where floor ${floor} and lift ${lift} and  rent ${rent} and region ${region} and unit_price between #{min} and #{max} GROUP BY orientation ORDER BY ROUND(AVG(unit_price)) desc) as a\n" +
            "where a.b>10 limit 10")
    List<Subway_type> orientation_price(double min,double max,String rent,String region,String floor,String lift);

    @Select("select a.region,a.a,b.b,c.c\n" +
            "from\n" +
            "(select region,ROUND(avg(unit_price)) as a from spider where floor=\"低楼层\" and rent ${rent} and region ${region} and unit_price between #{min} and #{max} group by region) as a LEFT JOIN\n" +
            "(select region,ROUND(avg(unit_price)) as b from spider where floor=\"中楼层\" and rent ${rent} and region ${region} and unit_price between #{min} and #{max} group by region) as b on a.region=b.region LEFT JOIN\n" +
            "(select region,ROUND(avg(unit_price)) as c from spider where floor=\"高楼层\" and rent ${rent} and region ${region} and unit_price between #{min} and #{max} group by region) as c on a.region=c.region")
    List<Floor_type> Floor_price(double min,double max,String rent,String region,String floor,String lift);



}
